﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Diagnostics;
using System.Linq;
using System.Text;
using Workbench.Models;
using Workbench.Shared;

namespace Workbench.Persistance
{
    public class LemmatizerRepository : RepositoryBase
    {
        public LemmatizerRepository(Func<SQLiteConnection> connectionProvider) : base(connectionProvider) { }


        public List<WordModel> GetAllWords(long sentenceId)
        {
            var con = connectionProvider();
            var cmd = new SQLiteCommand(
@"SELECT w.RowId, w.SentenceId, uw.RowId, uw.Content, bf.RowId, bf.Content
FROM Words w
INNER JOIN Sentences s on s.RowId = w.SentenceId
LEFT JOIN UniqueWords uw ON uw.RowId = w.UniqueWordId
LEFT JOIN BaseForms bf ON bf.RowId = w.BaseFormId
WHERE s.RowId = @sentenceId
ORDER BY w.RowId", con);
            cmd.Parameters.AddWithValue("@sentenceId", sentenceId);

            var result = new List<WordModel>();
            DbUtility.ExecuteReader(cmd, (reader) =>
            {
                result.Add(ReadWord(reader));
            });

            return result;
        }

        public List<WordModel> GetAllWords(ProjectFileModel file)
        {
            var con = connectionProvider();
            var cmd = new SQLiteCommand(
@"SELECT w.RowId, w.SentenceId, uw.RowId, uw.Content, bf.RowId, bf.Content
FROM Words w
INNER JOIN Sentences s on s.RowId = w.SentenceId
INNER JOIN Paragraphs p on p.RowId = s.Paragraphid
LEFT JOIN UniqueWords uw ON uw.RowId = w.UniqueWordId
LEFT JOIN BaseForms bf ON bf.RowId = w.BaseFormId
WHERE p.fileId = @fileID", con);
            cmd.Parameters.AddWithValue("@fileId", file.RowId);

            var result = new List<WordModel>();
            DbUtility.ExecuteReader(cmd, (reader) => 
            {
                result.Add(ReadWord(reader));
            });

            return result;
        }

        private WordModel ReadWord(SQLiteDataReader reader)
        {
            var model = new WordModel();
            bool uniqueWordIsNull = false;
            bool baseFormIsNull = false;
            model.RowId = reader.GetInt64(0);
            string content = null;
            if (!reader.IsDBNull(1)) model.SentenceId = reader.GetInt64(1);

            if (!reader.IsDBNull(2)) model.UniqueWordId = reader.GetInt64(2); else uniqueWordIsNull = true;
            if (!reader.IsDBNull(3)) content = reader.GetString(3);
            if (!uniqueWordIsNull)
            {
                model.UniqueWord = new UniqueWordModel()
                {
                    Content = content,
                    RowId = model.UniqueWordId
                };
            }
            if (!reader.IsDBNull(4)) model.BaseFormId = reader.GetInt64(4); else baseFormIsNull = true;
            if (!reader.IsDBNull(5)) content = reader.GetString(5);
            if (!baseFormIsNull)
            {
                model.BaseForm = new BaseFormModel()
                {
                    RowId = model.BaseFormId,
                    Content = content
                };
            }

            return model;
        }

        public List<WordModel> GetAllWords(ProjectFileModel file, Dictionary<long, POSTypeModel> posTypes)
        {
            var words = GetAllWords(file);
            Dictionary<long, WordModel> wordsById = words.ToDictionary(x => x.RowId);
            using (var reader = DbUtility.ExecuteReader(connectionProvider(),
                                    @"SELECT WordId, POSTypeId FROM Words_POSTypes"))
            {
                long wordId, posTypeId;
                WordModel word = null;
                POSTypeModel posType = null;
                while (reader.Read())
                {
                    wordId = reader.GetInt64(0);
                    posTypeId = reader.GetInt64(1);

                    wordsById.TryGetValue(wordId, out word);
                    posTypes.TryGetValue(posTypeId, out posType);

                    if (word == null || posType == null) continue;
                    word.POSTypes.Add(posType);
                }
            }
            return words;
        }

        public List<BaseFormModel> GetAllBaseForms()
        {
            var result = new List<BaseFormModel>();
            try
            {
                using (var reader = DbUtility.ExecuteReader(connectionProvider(),
                                        @"SELECT RowId, Content FROM BaseForms"))
                {
                    while (reader.Read())
                    {
                        var model = new BaseFormModel();
                        var index = 0;
                        model.RowId = reader.GetInt64(index);
                        index++; if (!reader.IsDBNull(index)) model.Content = reader.GetString(index);

                        result.Add(model);
                    }
                }
            }
            catch (SQLiteException ex)
            {
                LoggingService.LogError("DAL::LemmatizerRepository - Failed to get word base forms", ex);
                throw new WorkbenchDALException("Failed to get word base forms");
            }
            return result;
        }

        public List<BaseFormModel> GetBaseFormSuggestions(string currentBaseWordContent)
        {
            var result = new List<BaseFormModel>();
            var parameters = new Dictionary<string, object>();
            parameters.Add("@contentStart", string.Format("{0}%", currentBaseWordContent));
            var cmdText =
@"SELECT bf.RowId, bf.Content 
FROM BaseForms bf
WHERE bf.Content LIKE @contentStart
ORDER BY bf.Content
LIMIT 100";
            using (var reader = DbUtility.ExecuteReader(connectionProvider(), cmdText, parameters))
            {
                while (reader.Read())
                {
                    var model = new BaseFormModel();
                    var index = 0;
                    model.RowId = reader.GetInt64(index);
                    index++; if (!reader.IsDBNull(index)) model.Content = reader.GetString(index);

                    result.Add(model);
                }
            }
            return result;
        }

        public void PersistBaseForm(BaseFormModel baseForm)
        {
            var con = connectionProvider();
            using (var transaction = con.BeginTransaction())
            {
                long rowId;
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = "SELECT RowId FROM BaseForms WHERE Content = @Content";
                    cmd.Parameters.AddWithValue("@Content", baseForm.Content);
                    rowId = DbUtility.ExecuteScalar<long>(cmd);
                }
                if (rowId == 0)
                {
                    using (var cmd = con.CreateCommand())
                    {
                        cmd.Transaction = transaction;
                        cmd.CommandText = "INSERT INTO BaseForms (Content) VALUES (@Content); SELECT last_insert_rowid();";
                        cmd.Parameters.AddWithValue("@Content", baseForm.Content);
                        rowId = DbUtility.ExecuteScalar<long>(cmd);
                    }
                }
                transaction.Commit();
                baseForm.RowId = rowId;
            }
        }

        public void DeleteBaseForm(BaseFormModel baseForm)
        {
            var con = connectionProvider();
            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = "UPDATE Words SET BaseFormId = NULL WHERE BaseFormId = @BaseFormId";
                    cmd.Parameters.AddWithValue("@BaseFormId", baseForm.RowId);
                    cmd.ExecuteNonQuery();
                }
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = "DELETE FROM BaseForms WHERE RowId = @RowId";
                    cmd.Parameters.AddWithValue("@RowId", baseForm.RowId);
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
                baseForm.RowId = 0;
            }
        }

        public void UpdateBaseFormForWords(BaseFormModel baseForm, IEnumerable<WordModel> words)
        {
            var con = connectionProvider();

            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText = "UPDATE Words SET BaseFormId = ? WHERE RowId = ?";

                    var pRowId = new SQLiteParameter();
                    var pBaseFormId = new SQLiteParameter();
                    if (baseForm.RowId == 0)
                    {
                        pBaseFormId.Value = DBNull.Value;
                    }
                    else
                    {
                        pBaseFormId.Value = baseForm.RowId;
                    }
                    cmd.Parameters.Add(pBaseFormId);
                    cmd.Parameters.Add(pRowId);

                    foreach (var word in words)
                    {
                        pRowId.Value = word.RowId;
                        cmd.ExecuteNonQuery();
                    }
                }
                transaction.Commit();
            }
            foreach (var word in words)
            {
                word.BaseForm = baseForm;
                word.BaseFormId = baseForm.RowId;
            }
        }

        public void UpdateBaseFormForWords(long baseFormId, long uniqueWordId, long fileId)
        {
            var con = connectionProvider();
            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText =
@"UPDATE Words 
SET BaseFormId = @baseFormId 
WHERE UniqueWordId = @uniqueWordId
AND RowId IN (
    SELECT w.RowId 
    FROM Words w
    INNER JOIN Sentences s ON s.RowId = w.SentenceId
    INNER JOIN ParagraphS p ON p.RowId = s.ParagraphId
    WHERE p.FileId = @fileId
);";
                    cmd.Parameters.AddWithValue("@baseFormId", DbUtility.GetForeignKeyValue(baseFormId));
                    cmd.Parameters.AddWithValue("@uniqueWordId", uniqueWordId);
                    cmd.Parameters.AddWithValue("@fileId", fileId);
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

        public void UpdateBaseFormForWords(long baseFormId, long uniqueWordId)
        {
            var con = connectionProvider();
            using (var transaction = con.BeginTransaction())
            {
                using (var cmd = con.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    cmd.CommandText =
@"UPDATE Words 
SET BaseFormId = @baseFormId 
WHERE UniqueWordId = @uniqueWordId;";
                    cmd.Parameters.AddWithValue("@baseFormId", DbUtility.GetForeignKeyValue(baseFormId));
                    cmd.Parameters.AddWithValue("@uniqueWordId", uniqueWordId);
                    cmd.ExecuteNonQuery();
                }
                transaction.Commit();
            }
        }

    }
}
